package com.lei.springbootpractice.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库调试控制器
 * 用于检查数据库表结构和数据
 */
@RestController
@RequestMapping("/api/debug")
public class DatabaseDebugController {

    @Autowired
    private DataSource dataSource;

    /**
     * 列出所有数据库表
     */
    @GetMapping("/tables")
    public Map<String, Object> listTables() {
        Map<String, Object> result = new HashMap<>();
        List<String> tables = new ArrayList<>();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tables.add(tableName);
            }
            
            result.put("success", true);
            result.put("tables", tables);
            result.put("tableCount", tables.size());
            
        } catch (SQLException e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }

    /**
     * 检查Category表结构
     */
    @GetMapping("/category-structure")
    public Map<String, Object> checkCategoryStructure() {
        Map<String, Object> result = new HashMap<>();
        List<Map<String, String>> columns = new ArrayList<>();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 检查Category表
            ResultSet rs = metaData.getColumns(null, null, "Category", null);
            
            while (rs.next()) {
                Map<String, String> column = new HashMap<>();
                column.put("name", rs.getString("COLUMN_NAME"));
                column.put("type", rs.getString("TYPE_NAME"));
                column.put("size", rs.getString("COLUMN_SIZE"));
                column.put("nullable", rs.getString("IS_NULLABLE"));
                columns.add(column);
            }
            
            result.put("success", true);
            result.put("tableName", "Category");
            result.put("columns", columns);
            result.put("columnCount", columns.size());
            
        } catch (SQLException e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }

    /**
     * 检查索引信息
     */
    @GetMapping("/indexes")
    public Map<String, Object> checkIndexes() {
        Map<String, Object> result = new HashMap<>();
        List<Map<String, String>> indexes = new ArrayList<>();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 检查Category表的索引
            ResultSet rs = metaData.getIndexInfo(null, null, "Category", false, false);
            
            while (rs.next()) {
                Map<String, String> index = new HashMap<>();
                index.put("indexName", rs.getString("INDEX_NAME"));
                index.put("columnName", rs.getString("COLUMN_NAME"));
                index.put("unique", String.valueOf(rs.getBoolean("NON_UNIQUE")));
                indexes.add(index);
            }
            
            result.put("success", true);
            result.put("categoryIndexes", indexes);
            
        } catch (SQLException e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }

    /**
     * 验证数据库连接
     */
    @GetMapping("/connection-test")
    public Map<String, Object> testDatabaseConnection() {
        Map<String, Object> result = new HashMap<>();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            result.put("success", true);
            result.put("databaseProductName", metaData.getDatabaseProductName());
            result.put("databaseProductVersion", metaData.getDatabaseProductVersion());
            result.put("driverName", metaData.getDriverName());
            result.put("url", metaData.getURL());
            result.put("userName", metaData.getUserName());
            
        } catch (SQLException e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }
} 